from sqlalchemy import MetaData, create_engine
from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import sessionmaker

url = 'mysql+pymysql://root:9999@localhost:3306/zxzl?charset=utf8'
engine = create_engine(url, pool_size=10, max_overflow=3, echo_pool=True)
metadata = MetaData()
metadata.reflect(bind=engine, only=['guest', 'guest_link_contract'])
Base = automap_base(metadata=metadata)
session = sessionmaker(bind=engine)
Base.prepare()


# 插入guest表：默认不输入legal_name和legal_tel时与contact相同
def insert_guest(company_name, tax_payer, legal_name, legal_tel,
                 company_addr, company_email):

        guest = Base.classes.guest
        sess = session()


        new_guest = guest(company_name=company_name,
                          tax_payer=tax_payer,
                          company_addr=company_addr,
                          legal_name=legal_name,
                          legal_tel=legal_tel,
                          company_email=company_email)

        try:
                sess.add(new_guest)
                sess.commit()
                print('插入成功!')
                sess.close()
                return True
        except Exception as e:
                print(e)
                print('插入错误：数据库错误！')
                return False


def delete_guest(company_id):
        Guest = Base.classes.guest
        sess = session()

        exist = search_guest(company_id=company_id)
        if exist:
                guest = sess.query(Guest).filter_by(id=company_id).all()
                print(guest)
                try:
                        for i in guest:
                                sess.delete(i)
                        sess.commit()
                        print('删除成功！')
                        sess.close()
                        return True
                except Exception as e:
                        print(e)
                        return False
        else:
                print('pri_key不存在！')
                return False


# 更改客户信息:根据主键(company_id)来定位修改语句
def update_guest(company_id, company_name, tax_payer, company_addr,
                 legal_name, legal_tel, company_email):
        Guest = Base.classes.guest
        sess = session()

        tax_payer_exist = search_guest(tax_payer=tax_payer)
        id_exist = search_guest(company_id=company_id)
        if id_exist:     # 如果主键存在
                if tax_payer_exist:     # 如果新的纳税人识别号是已存在的
                        if id != company_id:    # 如果要tax_payer在另一条上则有unique错误
                                print('tax_payer与别人的重复！')
                                return False
        else:
                print('pri_key不存在！')
                return False

        guest = sess.query(Guest).filter_by(id=company_id).all()[0]
        guest.company_name = company_name
        guest.tax_payer = tax_payer
        guest.company_addr = company_addr
        guest.legal_name = legal_name
        guest.legal_tel = legal_tel
        guest.company_email = company_email

        try:
                sess.commit()
                print('更改成功！')
                sess.close()
                return True
        except Exception as e:
                print(e)
                return False


# ①：只传company_name按主键查找
# ②：只传tax_payer按纳税人识别号查找
# ③：不传参数进行完全查找
def search_guest(company_id=None, tax_payer=None):
        Guest = Base.classes.guest
        sess = session()

        query = sess.query(Guest)

        if tax_payer:    # 根据纳税人识别号
                guest = query.filter_by(tax_payer=tax_payer).all()
        elif company_id:   # 根据主键查找
                guest = query.filter_by(id=company_id).all()
        else:   # 完全查找
                guest = query.all()

        # 将读库的[(), ()]格式dump为[{}, {}]格式
        guest_dump = [{'id': guest[i].id, 'company_name': guest[i].company_name,
                       'tax_payer': guest[i].tax_payer, 'legal_name': guest[i].legal_name,
                       'legal_tel': guest[i].legal_tel, 'company_addr': guest[i].company_addr,
                       'company_email': guest[i].company_email}
                      for i in range(len(guest))]

        try:
                sess.commit()
                sess.close()
                # print(guest_dump)
                return guest_dump
        except Exception as e:
                print(e)


if __name__ == '__main__':
    # insert_guest(company_name='武汉理工大学', contact_name='孙宇峰',
    #              contact_tel='15342344775', tax_payer='1456453457684MK',
    #              company_addr='湖北武汉洪山区马房山')
    # insert_guest(company_name='武汉大学', contact_name='梁润颖',
    #              contact_tel='15342344776', tax_payer='1456453457684ML',
    #              company_addr='湖北武汉洪山区珞珈山')
        pass


